/**
	exec SP_SALES_FORECAST_ANALYSIS_REPORT 0, 0, 0, 0, 0, 'byMonth', 0, 2009, 0, 24,'bySalesPerson', 'forecast',''
	select * from fcentrymaster
*/
ALTER PROC SP_SALES_FORECAST_ANALYSIS_REPORT 
AS
BEGIN
	
	DECLARE @USERLIST1_LABEL VARCHAR(50)
	DECLARE @USERLIST2_LABEL VARCHAR(50)
	DECLARE @QUERY_STR NVARCHAR(2000)
	DECLARE @YEAR_TO INT
	DECLARE @YEAR_FROM INT
	DECLARE @MONTH_FROM INT
	DECLARE @MONTH_TO INT
	DECLARE @SHOW_NEXT INT
	DECLARE @TMP_DATE DATETIME

	SET @YEAR_FROM = YEAR(CURRENT_TIMESTAMP)
	SET @MONTH_FROM = MONTH(CURRENT_TIMESTAMP)-1
	SET @SHOW_NEXT = 24
	--IF (@REPORT_TYPE = 'forecast') 
	--BEGIN
		SELECT @USERLIST1_LABEL = USERLIST1, @USERLIST2_LABEL = USERLIST2 FROM PROJSETUP WHERE PROJID = (SELECT MAX(PROJID) FROM PROJSETUP)
		SET @QUERY_STR = N' --INSERT INTO #RESULTS
			SELECT C.Customer_ID,C.C_NAME,
				EC.C_NAME AS END_CUSTOMER,				
				SP.SALESPERSON_NAME, 
				PS.PROJNAME, P.ProcessStep_ID, P.P_Parent_Product_ID,
				P.P_MARKETING_PART_NUMBER, SP.SalesPerson_ID,
				USERLIST1VAL,
				USERLIST2VAL,				
				MONTH,
				YEAR,
				QTY,
				PRICE --select * from FCENTRYMASTER where  price> 0
			FROM PROJSETUP PS, CUSTOMER C, PRODUCT P, SALES_PERSONS SP, FCENTRYMASTER FCEM
			LEFT OUTER JOIN CUSTOMER EC ON EC.CUSTOMER_ID = FCEM.CUSTID
			WHERE FCEM.PROJID = PS.PROJID
				AND C.CUSTOMER_ID = PS.CUSTID
				AND P.PRODUCT_ID = FCEM.PRODID
				AND SP.SALESPERSON_ID = PS.SPERSONID
				AND PS.PROJDISABLED = 1 '
		
		/*IF @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND PS.CUSTID = ' + CONVERT(VARCHAR, @CUSTOMER_ID)
		END
		IF @END_CUSTOMER_ID IS NOT NULL AND @END_CUSTOMER_ID > 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND PS.ODMID = ' + CONVERT(VARCHAR, @END_CUSTOMER_ID)
		END
		IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID > 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND FCEM.PRODID = ' + CONVERT(VARCHAR, @PRODUCT_ID)
		END
		IF @SALESPERSON_ID IS NOT NULL AND @SALESPERSON_ID > 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND PS.SPERSONID = ' + CONVERT(VARCHAR, @SALESPERSON_ID)
		END
		IF @CONF_LEVEL IS NOT NULL AND @CONF_LEVEL >= 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND FCEM.CONFIDENCE_LEVEL >= ' + CONVERT(VARCHAR, @CONF_LEVEL)
		END
		IF @USERLIST1VAL_INPUT IS NOT NULL AND LEN(RTRIM(LTRIM(@USERLIST1VAL_INPUT))) > 0
		BEGIN
			SET @QUERY_STR = @QUERY_STR + ' AND PS.USERLIST1VAL = ''' + @USERLIST1VAL_INPUT + ''''
		END*/
	
		/*IF ( @PERIOD = 'byQuarter' )
		BEGIN
			IF ( @QTR_NO = 1 )
			BEGIN
				SET @MONTH_FROM = 0
				SET @MONTH_TO = 2
			END
			ELSE IF ( @QTR_NO = 2 )
			BEGIN
				SET @MONTH_FROM = 3
				SET @MONTH_TO = 5
			END
			ELSE IF ( @QTR_NO = 3 )
			BEGIN
				SET @MONTH_FROM = 6
				SET @MONTH_TO = 8
			END
			ELSE
			BEGIN
				SET @MONTH_FROM = 9
				SET @MONTH_TO = 11
			END
		END
		ELSE --byMonth
		BEGIN*/
			/*IF ( @MONTH_TO < @MONTH_FROM )
			BEGIN
				SET @YEAR_TO = @YEAR_FROM+1
			END*/
			SET @TMP_DATE = CAST( (CONVERT(VARCHAR,(@MONTH_FROM+1)) + '/01/' + CONVERT(VARCHAR,@YEAR_FROM)) AS DATETIME)
			
			IF ( @SHOW_NEXT > 0 ) 
				SET @TMP_DATE = DATEADD(M, (@SHOW_NEXT-1), @TMP_DATE)
			
			SET @MONTH_TO = MONTH(@TMP_DATE)-1 --STARTS FROM ZERO
			SET @YEAR_TO = YEAR(@TMP_DATE)
		--END
		
		--SET @QUERY_STR = @QUERY_STR + ' AND ( FCEM.MONTH >= '+ CONVERT(VARCHAR,@MONTH_FROM) +' AND FCEM.YEAR >= ' +CONVERT(VARCHAR, @YEAR_FROM) + ') AND ( FCEM.MONTH <= ' + CONVERT(VARCHAR, @MONTH_TO) + ' AND FCEM.YEAR <= ' + CONVERT(VARCHAR, @YEAR_TO)+ ')'
		SET @QUERY_STR = @QUERY_STR + 'AND CONVERT( datetime,( CONVERT(char(2),(month+1))+''/01/''+ CONVERT(char(4), year )), 101)  >=  '''+ CONVERT(varchar,(@MONTH_FROM+1)) + '/01/' + CONVERT(char(4),@YEAR_FROM)+''''
		SET @QUERY_STR = @QUERY_STR + 'AND CONVERT( datetime,( CONVERT(char(2),(month+1))+''/01/''+ CONVERT(char(4), year )), 101)  <=  '''+ CONVERT(varchar,(@MONTH_TO+1)) + '/01/' + CONVERT(char(4),@YEAR_TO)+''''
		
		--CONVERT( datetime,( CONVERT(char(2),(month+1))+'/01/'+CONVERT(char(4), year )), 101)  >=   '09/01/2009' 

		--IF ( @GROUP_CATEGORY = 'bySalesPerson' )
			SET @QUERY_STR = @QUERY_STR + ' ORDER BY SP.SALESPERSON_NAME, P.P_MARKETING_PART_NUMBER, YEAR, MONTH'
		/*
		IF ( @GROUP_CATEGORY = 'byProduct' )
			SET @QUERY_STR = @QUERY_STR + ' ORDER BY P.P_MARKETING_PART_NUMBER, SP.SALESPERSON_NAME, YEAR, MONTH'

		IF ( @GROUP_CATEGORY = 'byEndCust' )
			SET @QUERY_STR = @QUERY_STR + ' ORDER BY EC.C_NAME, P.P_MARKETING_PART_NUMBER, SP.SALESPERSON_NAME, YEAR, MONTH'
		*/
	--END
	/*ELSE
	BEGIN
		SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, 'SALES' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL

	END*/
	--PRINT @QUERY_STR
	EXEC (@QUERY_STR)	
	
END